package com.example.teacherservice.repository;

import com.example.teacherservice.dto.ClassDTO;
import com.example.teacherservice.dto.CourseDTO;
import com.example.teacherservice.dto.StudentDTO;
import com.example.teacherservice.dto.StudentProgressDTO;
import com.example.teacherservice.entity.TeaClass;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface TeacherClassDetailRepository extends JpaRepository<TeaClass, Long> {

    // ========== JPA自动生成查询 ==========
    boolean existsByTeaIdAndClassId(Long teaId, Long classId);

    // ========== 自定义JPQL查询 ==========

    // 获取班级基本信息
    @Query("SELECT new com.example.teacherservice.dto.ClassDTO(c.id, c.className, c.classStuNum, c.createTime) " +
            "FROM Class c WHERE c.id = :classId AND c.deleted = 0")
    ClassDTO findClassById(@Param("classId") Long classId);

    // 获取班级学生列表（使用实际存在的user_id字段替代stuNo）
    @Query("SELECT new com.example.teacherservice.dto.StudentDTO(s.id, s.stuName, s.userId, s.createTime) " +
            "FROM StuClass sc JOIN Student s ON sc.stuId = s.id " +
            "WHERE sc.classId = :classId AND  s.deleted = 0")
    List<StudentDTO> findStudentsByClassId(@Param("classId") Long classId);

    // 获取班级课程列表（使用实际存在的couIntroduction字段替代couDesc）
    @Query("SELECT new com.example.teacherservice.dto.CourseDTO(c.id, c.couName, c.couIntroduction, c.createTime) " +
            "FROM CouClass cc JOIN Course c ON cc.courseId = c.id " +
            "WHERE cc.classId = :classId AND cc.status = 1 AND c.deleted = 0")
    List<CourseDTO> findCoursesByClassId(@Param("classId") Long classId);

    // ========== 原生SQL查询 (备用方案) ==========
    @Query(value = "SELECT EXISTS(SELECT 1 FROM tea_class WHERE tea_id = :teacherId AND class_id = :classId AND deleted = 0)",
            nativeQuery = true)
    boolean checkTeachingRelationship(@Param("teacherId") Long teacherId,
                                      @Param("classId") Long classId);


    @Query("SELECT COUNT(c) > 0 FROM Course c " +
            "WHERE c.id = :courseId AND c.teaId = :teaId AND c.deleted = 0")
    boolean existsByTeaIdAndCourseId(@Param("teaId") Long teaId,
                                     @Param("courseId") Long courseId);

    // ===== 学生进度查询 =====
    // 修正1：使用正确的表名和字段名
    // 修正后的原生SQL查询
    // 修正后的原生SQL查询（使用实际存在的列）
    @Query(value = "SELECT " +
            "lp.id as id, " +
            "lp.stu_id as stuId, " +
            "lp.cou_id as couId, " +
            "lp.cha_id as chaId, " +
            "lp.duration as learnedDuration, " +
            "lp.progress as mediaProgress, " +
            "NULL as mediaDuration, " + // 如果course表没有视频时长字段
            "lp.recording as learnedStatus, " +
            "lp.last_study_time as positioning " +
            "FROM learning_progress lp " +
            "JOIN course c ON lp.cou_id = c.id " +
            "WHERE lp.stu_id = :studentId " +
            "AND lp.cou_id IN (:courseIds) " +
            "AND lp.deleted = 0",
            nativeQuery = true)
    List<StudentProgressDTO> findStudentProgress(@Param("studentId") Long studentId,
                                                 @Param("courseIds") List<Long> courseIds);

    // 优化后的课程ID查询
    @Query(value = "SELECT c.id FROM course c " +
            "JOIN cou_class cc ON c.id = cc.course_id " +
            "WHERE cc.class_id = :classId " +
            "AND c.tea_id = :teacherId " +
            "AND c.deleted = 0",
            nativeQuery = true)
    List<Long> findTeachingCourseIds(@Param("teacherId") Long teacherId,
                                     @Param("classId") Long classId);


}